#!/usr/bin/env python
# -*- coding: utf-8 -*-

from web.manager.log_manager import LogManager
from django.db import connection
from django.db.models import Q
from web.dao.base_dao import BaseDao
from web.util.datetime_util import DatetimeUtil
from web.constants.datetime_format import DatetimeFormat
from web.constants.direction import Direction
from web.constants.filter_type import FilterType
from web.constants.variance_type import VarianceType
from web.config.robot8_config import Robot8Config
from web.models.commodity_future_date_contract_data import CommodityFutureDateContractData
from web.models.robot8_commodity_future_filter import Robot8CommodityFutureFilter
from web.models.model_commodity_future_date_data_macd_gold_cross import ModelCommodityFutureDateDataMACDGoldCross
from web.models.model_commodity_future_date_data_macd_dead_cross import ModelCommodityFutureDateDataMACDDeadCross
from web.models.model_commodity_future_date_data_close_price_ma5_gold_cross import \
    ModelCommodityFutureDateDataClosePriceMA5GoldCross
from web.models.model_commodity_future_date_data_close_price_ma5_dead_cross import \
    ModelCommodityFutureDateDataClosePriceMA5DeadCross
from web.models.model_commodity_future_date_data_hei_kin_ashi_up import ModelCommodityFutureDateDataHeiKinAshiUp
from web.models.model_commodity_future_date_data_hei_kin_ashi_down import ModelCommodityFutureDateDataHeiKinAshiDown
from web.models.model_commodity_future_date_data_kd_gold_cross import ModelCommodityFutureDateDataKDGoldCross
from web.models.model_commodity_future_date_data_kd_dead_cross import ModelCommodityFutureDateDataKDDeadCross

Logger = LogManager.get_logger(__name__)

"""
Robot8CommodityFutureFilter的dao类
"""


class Robot8CommodityFutureFilterDao(BaseDao):
    model_class = Robot8CommodityFutureFilter

    def truncate_table_robot8_commodity_future_filter(self):
        """
        清空robot8_commodity_future_filter表
        """

        with connection.cursor() as cursor:
            Logger.info("清空robot8_commodity_future_filter表")

            cursor.execute("truncate table robot8_commodity_future_filter")

    def insert_robot8_commodity_future_filter_by_not_holding_commodity_future(self, current_transaction_date):
        """
        向robot8_commodity_future_filter表中插入尚未持有的期货（不包括移仓换月的期货）
        """

        Logger.info("向robot8_commodity_future_filter表中插入尚未持有的期货（不包括移仓换月的期货）")

        with connection.cursor() as cursor:
            time_str = DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format_With_Line)
            sql = "insert into robot8_commodity_future_filter(code) " \
                  "select * from (select cfdcd.code from c_f_date_contract_data cfdcd " \
                  "join commodity_future_info cfi on cfi.code=FNC_EXTRACT_CONTRACT_CODE(cfdcd.code) " \
                  "where cfdcd.transaction_date = to_date('{0}', 'yyyy-mm-dd') and cfi.tradable=1 and cfdcd.code not in(" \
                  "select distinct rcftr.code " \
                  "from robot8_c_f_transact_record rcftr " \
                  "where (rcftr.sell_date is not null and rcftr.buy_date is null and rcftr.buy_lot is null) " \
                  "or (rcftr.buy_date is not null and rcftr.sell_date is null and rcftr.sell_lot is null)) " \
                  "and cfdcd.code not in(" \
                  "select rcfr.code from robot8_c_f_rollover rcfr where rcfr.finish!=1))".format(
                time_str)
            cursor.execute(sql)

    def update_direction_by_boll(self, current_transaction_date):
        """
        如果收盘价大于等于布林带中轨，则为牛股，否则为熊股
        """

        Logger.info("如果收盘价大于等于布林带中轨，则为牛股，否则为熊股")

        with connection.cursor() as cursor:
            cursor.execute("update robot8_commodity_future_filter t1 "
                           "set t1.direction=1 "
                           "where t1.code in("
                           "select t.code "
                           "from c_f_date_contract_data t "
                           "where t.close_price>=t.mb and t.transaction_date=to_date(%s,'yyyy-mm-dd'))",
                           [DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format)])
            cursor.execute("update robot8_commodity_future_filter t1 "
                           "set t1.direction=-1 "
                           "where t1.code in("
                           "select t.code "
                           "from c_f_date_contract_data t "
                           "where t.close_price<t.mb and t.transaction_date=to_date(%s,'yyyy-mm-dd'))",
                           [DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format)])

    def update_direction_by_last_week_kd_up_down(self, current_transaction_date):
        """
        如果周线级别上一周K大于等于上上周K，则为牛股，否则为熊股
        """

        Logger.info("如果周线级别上一周K大于等于上上周K，则为牛股，否则为熊股")

        robot8_commodity_future_filter_list = self.find_list(dict(), dict(), list())
        for robot8_commodity_future_filter in robot8_commodity_future_filter_list:
            with connection.cursor() as cursor:
                cursor.execute("select * from ("
                               "select * from c_f_week_contract_data cfwd "
                               "where cfwd.end_date<to_date(%s,'yyyy-mm-dd') "
                               "and cfwd.code=%s "
                               "order by cfwd.end_date desc) "
                               "where rownum<=2",
                               [DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format),
                                robot8_commodity_future_filter.code])
                tuple = cursor.fetchall()
                # 如果返回记录不足2条，则删除
                if tuple == None or len(tuple) != 2:
                    cursor.execute("delete robot8_commodity_future_filter t1 where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                    continue
                first_commodity_future_week_contract_data_tuple = tuple[0]
                second_commodity_future_week_contract_data_tuple = tuple[1]
                # 如果返回记录没有k，则删除
                if first_commodity_future_week_contract_data_tuple[28] == None or second_commodity_future_week_contract_data_tuple[
                    28] == None:
                    cursor.execute("delete robot8_commodity_future_filter t1 where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                    continue
                if first_commodity_future_week_contract_data_tuple[28] >= second_commodity_future_week_contract_data_tuple[28]:
                    cursor.execute("update robot8_commodity_future_filter t1 "
                                   "set t1.direction=1 "
                                   "where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                else:
                    cursor.execute("update robot8_commodity_future_filter t1 "
                                   "set t1.direction=-1 "
                                   "where t1.code=%s",
                                   [robot8_commodity_future_filter.code])

    def update_direction_by_last_week_kd_gold_cross_dead_cross(self, current_transaction_date):
        """
        如果周线级别上一周KD金叉，则为牛股，否则为熊股
        """

        Logger.info("如果周线级别上一周KD金叉，则为牛股，否则为熊股")

        robot8_commodity_future_filter_list = self.find_list(dict(), dict(), list())
        for robot8_commodity_future_filter in robot8_commodity_future_filter_list:
            with connection.cursor() as cursor:
                cursor.execute("select * from ("
                               "select * from commodity_future_week_contract_data cfwd "
                               "where cfwd.end_date<to_date(%s,'yyyy-mm-dd') "
                               "and cfwd.code=%s "
                               "order by cfwd.end_date desc) "
                               "where rownum<=1",
                               [DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format),
                                robot8_commodity_future_filter.code])
                tuple = cursor.fetchall()
                # 如果返回记录不足1条，则删除
                if tuple == None or len(tuple) != 1:
                    cursor.execute("delete robot8_commodity_future_filter t1 where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                    continue
                last_commodity_future_week_contract_data_tuple = tuple[0]
                # 如果返回记录没有kd，则删除
                if last_commodity_future_week_contract_data_tuple[28] == None or last_commodity_future_week_contract_data_tuple[
                    29] == None:
                    cursor.execute("delete robot8_commodity_future_filter t1 where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                    continue
                if last_commodity_future_week_contract_data_tuple[28] >= last_commodity_future_week_contract_data_tuple[29]:
                    cursor.execute("update robot8_commodity_future_filter t1 "
                                   "set t1.direction=1 "
                                   "where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                else:
                    cursor.execute("update robot8_commodity_future_filter t1 "
                                   "set t1.direction=-1 "
                                   "where t1.code=%s",
                                   [robot8_commodity_future_filter.code])

    def update_direction_by_last_week_close_price_gold_cross_dead_cross_ma5(self, current_transaction_date):
        """
        如果周线级别上一周收盘价大于等于上上周MA5，则为牛股，否则为熊股
        """

        Logger.info("如果周线级别上一周收盘价大于等于上上周MA5，则为牛股，否则为熊股")

        robot8_commodity_future_filter_list = self.find_list(dict(), dict(), list())
        for robot8_commodity_future_filter in robot8_commodity_future_filter_list:
            with connection.cursor() as cursor:
                cursor.execute("select * from ("
                               "select * from c_f_week_contract_data cfwd "
                               "where cfwd.end_date<to_date(%s,'yyyy-mm-dd') "
                               "and cfwd.code=%s "
                               "order by cfwd.end_date desc) "
                               "where rownum<=2",
                               [DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format),
                                robot8_commodity_future_filter.code])
                tuple = cursor.fetchall()
                # 如果返回记录不足2条，则删除
                if tuple == None or len(tuple) != 2:
                    cursor.execute("delete robot8_commodity_future_filter t1 where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                    continue
                first_commodity_future_week_contract_data_tuple = tuple[0]
                first_commodity_future_week_contract_data_tuple = tuple[1]
                # 如果返回记录没有close_price或MA5，则删除
                if first_commodity_future_week_contract_data_tuple[8] == None or first_commodity_future_week_contract_data_tuple[
                    17] == None:
                    cursor.execute("delete robot8_commodity_future_filter t1 where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                    continue
                if first_commodity_future_week_contract_data_tuple[8] >= first_commodity_future_week_contract_data_tuple[17]:
                    cursor.execute("update robot8_commodity_future_filter t1 "
                                   "set t1.direction=1 "
                                   "where t1.code=%s",
                                   [robot8_commodity_future_filter.code])
                else:
                    cursor.execute("update robot8_commodity_future_filter t1 "
                                   "set t1.direction=-1 "
                                   "where t1.code=%s",
                                   [robot8_commodity_future_filter.code])

    def filter_by_bull_short_line(self, current_transaction_date):
        """
        如果收盘价在牛熊线之上，则为牛股，否则为熊股
        """

        Logger.info("如果收盘价在牛熊线之上，则为牛股，否则为熊股")

        robot8_commodity_future_filter_list = self.find_list(dict(), dict(), list())
        for robot8_commodity_future_filter in robot8_commodity_future_filter_list:
            commodity_future_date_contract_data = CommodityFutureDateContractData.objects.filter(
                Q(code=robot8_commodity_future_filter.code) &
                Q(transaction_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                DatetimeFormat.Date_Format_With_Line)))[0]

            # 牛熊线
            bull_short_line = None
            if commodity_future_date_contract_data.ma250 != None and commodity_future_date_contract_data.bias250 <= Robot8Config.Bias_Threshold_Top \
                    and commodity_future_date_contract_data.bias250 >= Robot8Config.Bias_Threshold_Bottom:
                bull_short_line = commodity_future_date_contract_data.ma250
                if commodity_future_date_contract_data.close_price < bull_short_line:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance250
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance250
                    robot8_commodity_future_filter.direction = Direction.Down
                    self.update(robot8_commodity_future_filter)
                else:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance250
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance250
                    robot8_commodity_future_filter.direction = Direction.Up
                    self.update(robot8_commodity_future_filter)
                continue
            if commodity_future_date_contract_data.ma120 != None and commodity_future_date_contract_data.bias120 <= Robot8Config.Bias_Threshold_Top \
                    and commodity_future_date_contract_data.bias120 >= Robot8Config.Bias_Threshold_Bottom:
                bull_short_line = commodity_future_date_contract_data.ma120
                if commodity_future_date_contract_data.close_price < bull_short_line:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance120
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance120
                    robot8_commodity_future_filter.direction = Direction.Down
                    self.update(robot8_commodity_future_filter)
                else:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance120
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance120
                    robot8_commodity_future_filter.direction = Direction.Up
                    self.update(robot8_commodity_future_filter)
                continue
            if commodity_future_date_contract_data.ma60 != None and commodity_future_date_contract_data.bias60 <= Robot8Config.Bias_Threshold_Top \
                    and commodity_future_date_contract_data.bias60 >= Robot8Config.Bias_Threshold_Bottom:
                bull_short_line = commodity_future_date_contract_data.ma60
                if commodity_future_date_contract_data.close_price < bull_short_line:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance60
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance60
                    robot8_commodity_future_filter.direction = Direction.Down
                    self.update(robot8_commodity_future_filter)
                else:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance60
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance60
                    robot8_commodity_future_filter.direction = Direction.Up
                    self.update(robot8_commodity_future_filter)
                continue
            if commodity_future_date_contract_data.ma20 != None and commodity_future_date_contract_data.bias20 <= Robot8Config.Bias_Threshold_Top \
                    and commodity_future_date_contract_data.bias20 >= Robot8Config.Bias_Threshold_Bottom:
                bull_short_line = commodity_future_date_contract_data.ma20
                if commodity_future_date_contract_data.close_price < bull_short_line:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance20
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance20
                    robot8_commodity_future_filter.direction = Direction.Down
                    self.update(robot8_commodity_future_filter)
                else:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance20
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance20
                    robot8_commodity_future_filter.direction = Direction.Up
                    self.update(robot8_commodity_future_filter)
                continue
            if commodity_future_date_contract_data.ma10 != None and commodity_future_date_contract_data.bias10 <= Robot8Config.Bias_Threshold_Top \
                    and commodity_future_date_contract_data.bias10 >= Robot8Config.Bias_Threshold_Bottom:
                bull_short_line = commodity_future_date_contract_data.ma10
                if commodity_future_date_contract_data.close_price < bull_short_line:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance10
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance10
                    robot8_commodity_future_filter.direction = Direction.Down
                    self.update(robot8_commodity_future_filter)
                else:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance10
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance10
                    robot8_commodity_future_filter.direction = Direction.Up
                    self.update(robot8_commodity_future_filter)
                continue
            if commodity_future_date_contract_data.ma5 != None and commodity_future_date_contract_data.bias5 <= Robot8Config.Bias_Threshold_Top \
                    and commodity_future_date_contract_data.bias5 >= Robot8Config.Bias_Threshold_Bottom:
                bull_short_line = commodity_future_date_contract_data.ma5
                if commodity_future_date_contract_data.close_price < bull_short_line:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance5
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance5
                    robot8_commodity_future_filter.direction = Direction.Down
                    self.update(robot8_commodity_future_filter)
                else:
                    robot8_commodity_future_filter.variance_type = VarianceType.Variance5
                    robot8_commodity_future_filter.variance = commodity_future_date_contract_data.variance5
                    robot8_commodity_future_filter.direction = Direction.Up
                    self.update(robot8_commodity_future_filter)
                continue
            # 如果最后任何bias指标都无法确定哪一根均线可以作为牛熊线的话，则将前一个交易日的收盘价作为牛熊线。注意：现在数据库中last_close_price字段没有空值
            bull_short_line = commodity_future_date_contract_data.last_close_price
            if commodity_future_date_contract_data.close_price < bull_short_line:
                robot8_commodity_future_filter.variance_type = VarianceType.Variance2
                robot8_commodity_future_filter.variance = variance2
                robot8_commodity_future_filter.variance = Direction.Down
                self.update(robot8_commodity_future_filter)
                continue
            else:
                avg_close_price = (commodity_future_date_contract_data.last_close_price + commodity_future_date_contract_data.close_price) / 2
                variance2 = (commodity_future_date_contract_data.last_close_price - avg_close_price) ** 2 + \
                            (commodity_future_date_contract_data.close_price - avg_close_price) ** 2
                robot8_commodity_future_filter.variance_type = VarianceType.Variance2
                robot8_commodity_future_filter.variance = variance2
                robot8_commodity_future_filter.variance = Direction.Up
                self.update(robot8_commodity_future_filter)

    def filter_by_fund_per_commodity_future(self, current_transaction_date):
        """
        过滤条件：如果交易这个期货一手所需的资金大于
        max(total_assets) * Fund_Proportion_For_Trading_In_Total_Fund / Max_Holding_Comodity_Future_Number，则删除
        max(total_assets)表示所有账户的最大总资产
        """

        Logger.info(
            "过滤条件：如果交易这个期货一手所需的资金大于max(total_assets) * Fund_Proportion_For_Trading_In_Total_Fund / Holding_Future_Number，则删除")

        with connection.cursor() as cursor:
            cursor.execute("delete from robot8_commodity_future_filter t "
                           "where t.code in("
                           "select cfi.code "
                           "from commodity_future_info cfi "
                           "join c_f_date_contract_data cfdd on cfi.code=FNC_EXTRACT_CONTRACT_CODE(cfdd.code) "
                           "where cfdd.transaction_date=to_date(%s,'yyyy-mm-dd') "
                           "and cfi.tradable=1 "
                           "and (select max(r.total_assets) from robot8_account r)* %s / %s < cfdd.close_price * cfi.transaction_multiplier * cfi.company_deposit)",
                           [DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format),
                            float(Robot8Config.Fund_Proportion_For_Trading_In_Total_Fund),
                            float(Robot8Config.Max_Holding_Comodity_Future_Number)])

    def update_filter_type(self, current_transaction_date):
        """
        判断使用哪种算法
        """

        Logger.info("判断使用哪种算法")

        robot8_commodity_future_filter_list = self.find_list(dict(), dict(), list())
        if robot8_commodity_future_filter_list != None and len(robot8_commodity_future_filter_list) > 0:
            for robot8_commodity_future_filter in robot8_commodity_future_filter_list:
                # 成功率
                success_rate = 0.0
                # 哪种算法
                filter_type = None

                # 判断第二个交易日是否是新合约的第一个交易日，如果是，则跳过
                with connection.cursor() as cursor:
                    cursor.execute("select * from commodity_future_info cfi "
                                   "join (select * from ("
                                   "select * from c_f_date_contract_data t "
                                   "where t.code=%s and t.transaction_date>to_date(%s,'yyyy-mm-dd') "
                                   "order by t.transaction_date asc) "
                                   "where rownum<=1) cfdd on FNC_EXTRACT_CONTRACT_CODE(cfdd.code)=cfi.code "
                                   "where cfdd.price_change>cfi.price_limit*100 or cfdd.price_change<-cfi.price_limit*100",
                                   [robot8_commodity_future_filter.code,
                                    DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                 DatetimeFormat.Date_Format_With_Line)])
                    beyong_price_limit = cursor.fetchone()
                    if beyong_price_limit != None:
                        self.delete(robot8_commodity_future_filter)
                        Logger.info("期货[" + robot8_commodity_future_filter.code + "]下一个交易日是新合约的第一个交易日，因此日期[" +
                                    DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format_With_Line) +
                                    "]不再交易")
                        continue

                if Robot8Config.Transaction_Strategy != 4:
                    if Direction.Up == robot8_commodity_future_filter.direction:
                        # 判断是否macd金叉
                        model_commodity_future_date_data_macd_gold_cross_list = ModelCommodityFutureDateDataMACDGoldCross.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                buy_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                      DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_macd_gold_cross_list != None and len(
                                model_commodity_future_date_data_macd_gold_cross_list) != 0:
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_macd_gold_cross t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_macd_gold_cross t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.MACD_Gold_Cross

                        # 判断是否close_price金叉ma5
                        model_commodity_future_date_data_close_price_ma5_gold_cross_list = ModelCommodityFutureDateDataClosePriceMA5GoldCross.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                buy_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                      DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_close_price_ma5_gold_cross_list != None and len(
                                model_commodity_future_date_data_close_price_ma5_gold_cross_list) != 0:
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_c_p_ma5_g_c t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_c_p_ma5_g_c t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.Close_Price_Ma5_Gold_Cross

                        # 判断是否hei kin ashi上升趋势
                        model_commodity_future_date_data_hei_kin_ashi_up_list = ModelCommodityFutureDateDataHeiKinAshiUp.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                buy_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                      DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_hei_kin_ashi_up_list != None and len(
                                model_commodity_future_date_data_hei_kin_ashi_up_list) != 0:
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_hei_kin_ashi_up t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_hei_kin_ashi_up t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.Hei_Kin_Ashi_Up

                        # 判断是否KD金叉
                        model_commodity_future_date_data_kd_gold_cross_list = ModelCommodityFutureDateDataKDGoldCross.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                buy_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                      DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_kd_gold_cross_list != None and len(
                                model_commodity_future_date_data_kd_gold_cross_list) != 0:
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_kd_gold_cross t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_kd_gold_cross t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.KD_Gold_Cross

                    if Direction.Down == robot8_commodity_future_filter.direction:
                        # 判断是否macd死叉
                        model_commodity_future_date_data_macd_dead_cross_list = ModelCommodityFutureDateDataMACDDeadCross.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                sell_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                       DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_macd_dead_cross_list != None and len(
                                model_commodity_future_date_data_macd_dead_cross_list):
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_macd_dead_cross t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_macd_dead_cross t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.MACD_Dead_Cross

                        # 判断是否close_price死叉ma5
                        model_commodity_future_date_data_close_price_ma5_dead_cross_list = ModelCommodityFutureDateDataClosePriceMA5DeadCross.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                sell_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                       DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_close_price_ma5_dead_cross_list != None and len(
                                model_commodity_future_date_data_close_price_ma5_dead_cross_list):
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_c_p_ma5_d_c t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_c_p_ma5_d_c t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.Close_Price_Ma5_Dead_Cross

                        # 判断是否hei kin ashi下降趋势
                        model_commodity_future_date_data_hei_kin_ashi_down_list = ModelCommodityFutureDateDataHeiKinAshiDown.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                sell_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                       DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_hei_kin_ashi_down_list != None and len(
                                model_commodity_future_date_data_hei_kin_ashi_down_list):
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_hei_kin_ashi_down t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_hei_kin_ashi_down t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.Hei_Kin_Ashi_Down

                        # 判断是否KD死叉
                        model_commodity_future_date_data_kd_dead_cross_list = ModelCommodityFutureDateDataKDDeadCross.objects.filter(
                            Q(code=robot8_commodity_future_filter.code) & Q(
                                sell_date=DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                       DatetimeFormat.Date_Format_With_Line)))
                        if model_commodity_future_date_data_kd_dead_cross_list != None and len(
                                model_commodity_future_date_data_kd_dead_cross_list):
                            # 计算成功率
                            with connection.cursor() as cursor:
                                cursor.execute("select ("
                                               "select count(*) from mdl_c_f_date_kd_dead_cross t where t.code=%s and t.profit_loss>0)/"
                                               "(select count(*) from mdl_c_f_date_kd_dead_cross t where t.code=%s)*100 "
                                               "from dual",
                                               [robot8_commodity_future_filter.code,
                                                robot8_commodity_future_filter.code])
                                temp_success_rate = cursor.fetchone()[0]
                                if temp_success_rate > success_rate:
                                    success_rate = temp_success_rate
                                    filter_type = FilterType.KD_Dead_Cross

                    if success_rate == 0 and filter_type == None:
                        # 如果当前日期不是交易日，则删除
                        self.delete(robot8_commodity_future_filter)
                    else:
                        # 更新filter_type字段
                        robot8_commodity_future_filter.filter_type = filter_type
                        robot8_commodity_future_filter.success_rate = success_rate
                        self.update(robot8_commodity_future_filter)

                if Robot8Config.Transaction_Strategy == 4:
                    commodity_future_date_contract_data_queryset = CommodityFutureDateContractData.objects.filter(
                        Q(transaction_date=current_transaction_date) & Q(code=robot8_commodity_future_filter.code))
                    if commodity_future_date_contract_data_queryset == None or len(commodity_future_date_contract_data_queryset) == 0:
                        Logger.info("期货[" + robot8_commodity_future_filter.code + "]在日期[" + str(
                            current_transaction_date) + "]没有交易记录，跳过")
                        continue
                    commodity_future_date_contract_data = commodity_future_date_contract_data_queryset[0]
                    # 牛熊线
                    bull_short_line = None
                    if commodity_future_date_contract_data.ma250 != None and commodity_future_date_contract_data.bias250 <= Robot8Config.Bias_Threshold_Top \
                            and commodity_future_date_contract_data.bias250 >= Robot8Config.Bias_Threshold_Bottom:
                        bull_short_line = commodity_future_date_contract_data.ma250
                    if commodity_future_date_contract_data.ma120 != None and commodity_future_date_contract_data.bias120 <= Robot8Config.Bias_Threshold_Top \
                            and commodity_future_date_contract_data.bias120 >= Robot8Config.Bias_Threshold_Bottom:
                        bull_short_line = commodity_future_date_contract_data.ma120
                    if commodity_future_date_contract_data.ma60 != None and commodity_future_date_contract_data.bias60 <= Robot8Config.Bias_Threshold_Top \
                            and commodity_future_date_contract_data.bias60 >= Robot8Config.Bias_Threshold_Bottom:
                        bull_short_line = commodity_future_date_contract_data.ma60
                    if commodity_future_date_contract_data.ma20 != None and commodity_future_date_contract_data.bias20 <= Robot8Config.Bias_Threshold_Top \
                            and commodity_future_date_contract_data.bias20 >= Robot8Config.Bias_Threshold_Bottom:
                        bull_short_line = commodity_future_date_contract_data.ma20
                    if commodity_future_date_contract_data.ma10 != None and commodity_future_date_contract_data.bias10 <= Robot8Config.Bias_Threshold_Top \
                            and commodity_future_date_contract_data.bias10 >= Robot8Config.Bias_Threshold_Bottom:
                        bull_short_line = commodity_future_date_contract_data.ma10
                    if commodity_future_date_contract_data.ma5 != None and commodity_future_date_contract_data.bias5 <= Robot8Config.Bias_Threshold_Top \
                            and commodity_future_date_contract_data.bias5 >= Robot8Config.Bias_Threshold_Bottom:
                        bull_short_line = commodity_future_date_contract_data.ma5
                    if bull_short_line == None:
                        # 如果最后任何bias指标都无法确定哪一根均线可以作为牛熊线的话，则将前一个交易日的收盘价作为牛熊线。注意：现在数据库中last_close_price字段没有空值
                        bull_short_line = commodity_future_date_contract_data.last_close_price
                    if commodity_future_date_contract_data.close_price > bull_short_line:
                        robot8_commodity_future_filter.filter_type = FilterType.Close_Price_Gold_Cross_Bull_Short_Line
                        robot8_commodity_future_filter.direction = Direction.Up
                        self.update(robot8_commodity_future_filter)
                    else:
                        robot8_commodity_future_filter.filter_type = FilterType.Close_Price_Dead_Cross_Bull_Short_Line
                        robot8_commodity_future_filter.direction = Direction.Down
                        self.update(robot8_commodity_future_filter)

        else:
            Logger.warn("表robot8_commodity_future_filter为空")
